package databank;

import guest.GuestVo;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;

public class DataBank {
	//------------------------------------------------
	//       첨부파일 설정
	//------------------------------------------------
	int fileSize = 10*1024*1024;
	private String uploadPath = "C:\\Users\\jhta\\workspace\\web_1403_2_academy\\WebContent\\upload";
	

	String fileEncoding = "UTF-8";
	
	//------------------------------------------------
	//         JDBC 연결
	//------------------------------------------------
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE"; 
	String db_user = "hr";
	String db_pwd = "hr";

	Connection conn;
	
	DataBankVo vo;
	
	public DataBank(){
		try {
			// 1) 드라이버 로딩
			Class.forName(driver).newInstance();
			
			// 2) Connection 객체 생성
			conn = DriverManager.getConnection(url, db_user, db_pwd);
			
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	// 자료를 입력하는 부분
	public boolean insert(){
		boolean flag = false;
		try{
			
		MultipartRequest mr = new MultipartRequest(vo.getRequest(), uploadPath, fileSize, fileEncoding, new DefaultFileRenamePolicy());
		
		String mid = mr.getParameter("mid");
		String subject = mr.getParameter("subject");
		String content = mr.getParameter("content");
		String pwd = mr.getParameter("pwd");
		
		String orifile = mr.getOriginalFileName("filename");
		String sysfile = mr.getFilesystemName("filename");
		
		// DB 저장
		String sql = "insert into databank(serial, mid, subject, content, filename, pwd, mdate) values(seq_databank.nextval, ?, ?, ?, ?, ?, sysdate)";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, mid);
		ps.setString(2, subject);
		ps.setString(3, content);
		ps.setString(4, sysfile);
		ps.setString(5, pwd);
		
		int cnt = ps.executeUpdate();
		if(cnt>0) flag = true;
		
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return flag;
	}
	
	// 해당 글을 지우는 함수
	public boolean delete(){
		boolean flag = false;
		
		String sql = "delete from databank where serial=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());
			
			int cnt = ps.executeUpdate();
			
			if(cnt > 0){
				flag = true;
				
				File file = new File(getUploadPath() + "/" + vo.getFilename());
				if(file.exists()){
					file.delete();
				}else{
					
				}
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		
		return flag;
	}
	
	// 글 수정하는 부분
	public boolean modify(){
		boolean flag = false;
		
		try{
			
			MultipartRequest mr = new MultipartRequest(vo.getRequest(), uploadPath, fileSize, fileEncoding, new DefaultFileRenamePolicy());
			
			String subject = mr.getParameter("subject");
			String content = mr.getParameter("content");
			String pwd = mr.getParameter("pwd");
			String sysfile = mr.getFilesystemName("newfilename");  // 새로운 파일
			String oldfile = mr.getParameter("filename");  // 예전파일
			int serial = Integer.parseInt(mr.getParameter("serial"));
			
			PreparedStatement ps;
			String sql;
			int cnt = 0;
			
			if(oldfile == null || oldfile.equals("")){
				if(sysfile != null && sysfile != ""){
					// 첨부파일이 있고 내용이 수정된 경우
					sql = "update databank set subject=?, content=?, filename=? where serial=? and pwd=?";
				
					ps= conn.prepareStatement(sql);
					
					ps.setString(1, subject);
					ps.setString(2, content);
					ps.setString(3, sysfile);
					ps.setInt(4, serial);
					ps.setString(5, pwd);
					
					cnt = ps.executeUpdate();
					
					if(cnt>0) flag = true;
					
				} else {
					// 첨부파일 수정없이 내용만 수정된 경우
					sql = "update databank set subject=?, content=? where serial=? and pwd=?";
					
					ps= conn.prepareStatement(sql);
					
					ps.setString(1, subject);
					ps.setString(2, content);
					ps.setInt(3, serial);
					ps.setString(4, pwd);
					
					cnt = ps.executeUpdate();
					
					if(cnt>0) flag = true;
					
				}
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
	
		
		return flag;
		
	}
	// 첨부파일만 삭제
	public void fileDel(){
		
		String sql = "update databank set filename='' where serial=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());
			
			int cnt = ps.executeUpdate();
			
			if(cnt > 0){
				File file = new File(getUploadPath() + "/" + vo.getFilename());
				if(file.exists()) file.delete();
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
	
	
	// Vector 타입을 쓰는 이유는 목록을 뿌리는 경우는 DataBankVo의 값을 여러개 뿌려줘야하기 때문이다.
	public Vector<DataBankVo> list(){

		Vector<DataBankVo> v = new Vector<>();
		
		try{
		// DB 저장
		String sql = "select * from databank where mid like ? or subject like ? or content like ? order by mdate desc";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, "%" + vo.getFind() + "%");
		ps.setString(2, "%" + vo.getFind() + "%");
		ps.setString(3, "%" + vo.getFind() + "%");
		
		ResultSet rs = ps.executeQuery();
		
		while(rs.next()){
			DataBankVo vo = new DataBankVo();
			
			vo.setMid(rs.getString("mid"));
			vo.setSubject(rs.getString("subject"));
			vo.setContent(rs.getString("content"));
			vo.setSerial(rs.getInt("serial"));
			vo.setMdate(rs.getDate("mdate"));
			vo.setFilename(rs.getString("filename"));
			
			
			v.add(vo);	
		}
		
		rs.close();
		ps.close();
		conn.close();
		
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return v;
		
	}
	
	public DataBankVo view(){
		DataBankVo v = null;
		String sql = "select * from databank where serial=?";
		
		try{
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());
			
			ResultSet rs = ps.executeQuery();
			
			if(rs.next()){
				v = new DataBankVo();
				v.setSerial(rs.getInt("serial"));
				v.setMid(rs.getString("mid"));
				v.setSubject(rs.getString("subject"));
				v.setContent(rs.getString("content"));
				v.setMdate(rs.getDate("mdate"));
				v.setFilename(rs.getString("filename"));
			}
			
			rs.close();
			ps.close();
			conn.close();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return v;
	}
	

	public DataBankVo getVo() {
		return vo;
	}

	public void setVo(DataBankVo vo) {
		this.vo = vo;
	}
	
	public String getUploadPath() {
		return uploadPath;
	}

	
}
